<?php

namespace DataCube\DataCubeAggregation\Utils;

class ExcelHelperFunc
{

    public function parseMatrixRange($start, $end, $matrix)
    {
        $rows = count($matrix);
        $cols = count($matrix[0]);

        // 验证输入的范围是否有效  
        if ($start < 1 || $start > $rows || $end < 1 || $end > $rows) {
            return "无效的行范围";
        }

        if ($start < 1 || $start > $cols || $end < 1 || $end > $cols) {
            return "无效的列范围";
        }

        // 解析矩阵范围
        $parsedMatrix = [];
        for ($row = $start - 1; $row < $end; $row++) {
            $parsedRow = [];
            for ($col = $start - 1; $col < $end; $col++) {
                $parsedRow[] = $matrix[$row][$col];
            }
            $parsedMatrix[] = $parsedRow;
        }

        return $parsedMatrix;
    }

    public function checkCharacters(string $colTitle)
    {
        return preg_match_all('[^\d\w]', $colTitle);
    }

    public function splitColTitleAsList(string $colTitle): array
    {
        $pattern = '/(\d+)/';  
        $parts = preg_split($pattern, $colTitle, 2);  
        
        $englishPart = $parts[0];  
        $numberPart = $parts[1];
        return [strtoupper($englishPart), intval($numberPart)];
    }

    public function titleToNumber($columnTitle)
    {
        $ans = 0;
        $title_len = strlen($columnTitle);
        for ($i = 0; $i < $title_len; $i++) {
            $column_no = ord($columnTitle[$i]) - 64;
            $ans += 26 ** ($title_len - $i - 1) * $column_no;
        }

        return $ans;
    }

    /**
     * @param Integer $columnNumber
     * @return String
     */
    public function convertToTitle(int $columnNumber)
    {
        $arr = [
            0 => 'Z',
            1 => 'A',
            2 => 'B',
            3 => 'C',
            4 => 'D',
            5 => 'E',
            6 => 'F',
            7 => 'G',
            8 => 'H',
            9 => 'I',
            10 => 'J',
            11 => 'K',
            12 => 'L',
            13 => 'M',
            14 => 'N',
            15 => 'O',
            16 => 'P',
            17 => 'Q',
            18 => 'R',
            19 => 'S',
            20 => 'T',
            21 => 'U',
            22 => 'V',
            23 => 'W',
            24 => 'X',
            25 => 'Y',
        ];
        $str = '';
        for (;;) {
            if ($columnNumber < 27) {
                return $arr[$columnNumber % 26] . $str;
            }
            $num = $columnNumber % 26;
            if ($num == 0) {
                $columnNumber--;
            }
            $str = $arr[$num] . $str;
            $columnNumber /= 26;
        }
        return $str;
    }
}
